package br.com.sga.modelo.dao;

import br.com.sga.util.conexao.Pool;
import br.com.sga.erro.ConexaoException;
import br.com.sga.erro.DaoException;
import br.com.sga.modelo.entidade.Usuario;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author kenzo
 */
public class UsuarioDaoImpl implements UsuarioDao {

    private Pool pool;
    String sqlInserir = "INSERT INTO usuarios (nome,login,senha,status) VALUES(?,?,?,?)";
    String sqlAtualizar = "UPDATE usuarios SET nome=?,login=?,senha=?,dt_atualizacao=now(),status=? WHERE (id_usuario=?)";
    String sqlExcluir = "DELETE FROM usuarios WHERE (id_usuario=?)";
    String sqlConsultarId = "SELECT nome,login,dt_atualizacao,status FROM usuarios WHERE (id_usuario=?)";
    String sqlConsultarLogin = "SELECT id_usuario,nome,login,senha,dt_atualizacao,status FROM usuarios WHERE (login=?)";
    String sqlListarTodos = "SELECT id_usuario,nome,login,dt_atualizacao,status FROM usuarios";
    String sqlListarAtivos = "SELECT id_usuario,nome,login,dt_atualizacao,status FROM usuarios WHERE (status=1)";

    public UsuarioDaoImpl(Pool pool) {
        this.pool = pool;
    }

    @Override
    public void salvar(Usuario bean) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;

        try {
            pst = c.prepareStatement(sqlInserir);
            pst.setString(1, bean.getNome());
            pst.setString(2, bean.getLogin());
            pst.setString(3, bean.getSenha());
            pst.setInt(4, bean.getStatus());
            pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            pool.freeConnection(c);
        }

    }

    @Override
    public void atualizar(Usuario bean) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try {
            pst = c.prepareStatement(sqlAtualizar);
            pst.setString(1, bean.getNome());
            pst.setString(2, bean.getLogin());
            pst.setString(3, bean.getSenha());
            pst.setInt(4, bean.getStatus());
            pst.setInt(5, bean.getId());
            pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            pool.freeConnection(c);
        }
    }

    @Override
    public void excluir(Integer id) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try {
            pst = c.prepareStatement(sqlExcluir);
            pst.setInt(1, id);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            pool.freeConnection(c);
        }
    }

    @Override
    public Usuario getBean(Integer id) throws ConexaoException, DaoException {
        Usuario u = null;
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try {
            pst = c.prepareStatement(sqlConsultarId);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                u = new Usuario();
                u.setId(id);
                u.setDtAtualizacao(rs.getDate("dt_atualizacao"));
                u.setLogin(rs.getString("login"));
                u.setNome(rs.getString("nome"));
                u.setStatus(rs.getInt("status"));
            }

            pst.close();
            return u;
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            pool.freeConnection(c);
        }
    }

    @Override
    public Usuario getByLogin(String login) throws ConexaoException, DaoException {
        Usuario u = null;
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try {
            pst = c.prepareStatement(sqlConsultarLogin);
            pst.setString(1, login);
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                u = new Usuario();
                u.setId(rs.getInt("id_usuario"));
                u.setDtAtualizacao(rs.getDate("dt_atualizacao"));
                u.setLogin(rs.getString("login"));
                u.setSenha(rs.getString("senha"));
                u.setNome(rs.getString("nome"));
                u.setStatus(rs.getInt("status"));
            }

            pst.close();
            return u;
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            pool.freeConnection(c);
        }
    }

    @Override
    public List<Usuario> getBeans() throws ConexaoException, DaoException {
        Usuario u = null;
        List<Usuario> lista = new ArrayList<>();
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try {
            pst = c.prepareStatement(sqlListarTodos);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                u = new Usuario();
                u.setId(rs.getInt("id_usuario"));
                u.setDtAtualizacao(rs.getDate("dt_atualizacao"));
                u.setLogin(rs.getString("login"));
                u.setNome(rs.getString("nome"));
                u.setStatus(rs.getInt("status"));
                lista.add(u);
            }

            pst.close();
            return lista;
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            pool.freeConnection(c);
        }
    }
}
